package com.bafa.dbcache;

import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.TreeMap;
import java.util.TreeSet;

import com.bafa.jdbc.DBAccess;
import com.bafa.jdbc.QueryObject;
import com.bafa.objects.Division;
import com.bafa.objects.LeaguePosition;
import com.bafa.objects.Score;
//import com.bafa.objects.Scorer;
import com.bafa.objects.Season;
//import com.bafa.objects.Team;
import com.bafa.objects.TeamAward;
import com.bafa.objects.schema.*;
//import com.bafa.objects.TeamSeason;
//import com.bafa.objects.TeamStats;
import com.bafa.services.RetrieveServices;
import com.bafa.util.comparators.TopScorerComparator;

public class GrabTeamStats {
	
	private static TreeMap teamstats = new TreeMap();
	static void grabTeamStats(DBAccess db)throws Exception{
		
		QueryObject teamsQuery = new QueryObject("teams");
		ResultSet teamsResult = db.query(teamsQuery);
		while(teamsResult.next()){
			ObjectFactory factory=new ObjectFactory();
			TeamStats stats = factory.createTeamStats();
			int teamId = teamsResult.getInt("ID");
			QueryObject seasonsQuery = new QueryObject("season");
			seasonsQuery.addOrderBy("year",true);
			seasonsQuery.addOrderBy("id",false);
			ResultSet seasonsResult = db.query(seasonsQuery);
			
			while (seasonsResult.next()){
				int seasonId = seasonsResult.getInt("ID");
				TeamSeason teamSeason = factory.createTeamSeason();
				setTeamSeasonStats(db,seasonId,teamId,stats);
			}
			
			// Grab Total Stats
			grabAllTimeGoalScorers(db,teamId,stats);
			grabTotalGamesWon(db,stats,teamId);
			grabTotalGamesDrawn(db,stats,teamId);
			grabTotalGamesLost(db,stats,teamId);
			grabTotalGoalsScored(db,stats,teamId);
			grabTotalGoalsConceded(db,stats,teamId);
			grabTotalCleanSheets(db,stats,teamId);
			teamstats.put(teamId,stats);
		}
		
		
	}
	
	public static TeamStats grabTeamStats(DBAccess db,Team team)throws Exception{
		
		ObjectFactory factory=new ObjectFactory();
		TeamStats stats = factory.createTeamStats();
		
		QueryObject seasonsQuery = new QueryObject("season");
		seasonsQuery.addOrderBy("year",true);
		seasonsQuery.addOrderBy("id",false);
		ResultSet seasonsResult = db.query(seasonsQuery);
		
		while (seasonsResult.next()){
			int seasonId = seasonsResult.getInt("ID");
			TeamSeason teamSeason = factory.createTeamSeason();
			
			teamSeason.setSeasonId(seasonId);
			setTeamSeasonStats(db,seasonId,team.getTeamDetails().getID(),stats);
		}
		
		//		 Grab Total Stats
		grabAllTimeGoalScorers(db,team.getTeamDetails().getID(),stats);
		grabTotalGamesWon(db,stats,team.getTeamDetails().getID());
		grabTotalGamesDrawn(db,stats,team.getTeamDetails().getID());
		grabTotalGamesLost(db,stats,team.getTeamDetails().getID());
		grabTotalGoalsScored(db,stats,team.getTeamDetails().getID());
		grabTotalGoalsConceded(db,stats,team.getTeamDetails().getID());
		grabTotalCleanSheets(db,stats,team.getTeamDetails().getID());
		grabTotalGamesPlayed(db,stats,team.getTeamDetails().getID());
		grabBiggestWin(db, stats, team.getTeamDetails().getID());
		grabBiggestDefeat(db, stats, team.getTeamDetails().getID());
		grabMostScored(db, stats, team.getTeamDetails().getID());
		
		team.setTeamStats(stats);
                return stats;
		
	}
	
	static TeamStats grabTeamStats(DBAccess db,Team team, int seasonId)throws Exception{
		ObjectFactory factory=new ObjectFactory();
                
		TeamStats stats = team.getTeamStats();
		for(int i=0;i<stats.getTeamSeason().size();i++){
			TeamSeason season = stats.getTeamSeason().get(i);
			if(season.getSeasonId()==seasonId) stats.getTeamSeason().remove(i);
		}
		QueryObject seasonsQuery = new QueryObject("season");
		seasonsQuery.addOrderBy("year",true);
		seasonsQuery.addOrderBy("id",false);
		seasonsQuery.addWhereAndField("id", ""+seasonId);
		ResultSet seasonsResult = db.query(seasonsQuery);
		
		while (seasonsResult.next()){
			
			TeamSeason teamSeason = factory.createTeamSeason();
			
			teamSeason.setSeasonId(seasonId);
			setTeamSeasonStats(db,seasonId,team.getTeamDetails().getID(),stats);
		}
		
//		 Grab Total Stats
		grabAllTimeGoalScorers(db,team.getTeamDetails().getID(),stats);
		grabTotalGamesWon(db,stats,team.getTeamDetails().getID());
		grabTotalGamesDrawn(db,stats,team.getTeamDetails().getID());
		grabTotalGamesLost(db,stats,team.getTeamDetails().getID());
		grabTotalGoalsScored(db,stats,team.getTeamDetails().getID());
		grabTotalGoalsConceded(db,stats,team.getTeamDetails().getID());
		grabTotalCleanSheets(db,stats,team.getTeamDetails().getID());
		grabTotalGamesPlayed(db,stats,team.getTeamDetails().getID());
		grabBiggestWin(db, stats, team.getTeamDetails().getID());
		grabBiggestDefeat(db, stats, team.getTeamDetails().getID());
		grabMostScored(db, stats, team.getTeamDetails().getID());
		team.setTeamStats(stats);
		return stats;
	}
	
	private static void setTeamSeasonStats(DBAccess db, int seasonId, int teamId,  TeamStats stats)throws Exception{
		GrabTeamSeason.grabTeamSeason(db, seasonId, teamId, stats);
	}
	
	private static void grabTotalCleanSheets(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from matches where ");
		sql.append("teama ="+teamId);
		sql.append(" and goalsb=0");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		int cleanSheets = 0;
		cleanSheets = matchesResult.getInt(1);
		
		sql = new StringBuffer();
		sql.append("select count(*) from matches where ");
		sql.append("teamb ="+teamId);
		sql.append(" and goalsa=0");
		matchesQuery.setQueryString(sql.toString());
		matchesResult = db.query(matchesQuery);
		matchesResult.next();
		cleanSheets = cleanSheets + matchesResult.getInt(1);
		stats.setCleanSheets(cleanSheets);
		
	}
	
	
	private static void grabBiggestWin(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		ObjectFactory factory = new ObjectFactory();
		com.bafa.objects.schema.Score biggestWin1 = factory.createScore();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from matches where ");
		sql.append("teama ="+teamId);
		sql.append(" order by goalsa-goalsb desc");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		int goalsAA =0;
		int goalsAB =0;
		int matchIdA = 0;
		if(matchesResult.next()){
			goalsAA = matchesResult.getInt("goalsa");
			goalsAB = matchesResult.getInt("goalsb");
			matchIdA = matchesResult.getInt("id");
		}
		
		
		sql = new StringBuffer();
		sql.append("select * from matches where ");
		sql.append("teamb ="+teamId);
		sql.append(" order by goalsb-goalsa desc");
		matchesQuery.setQueryString(sql.toString());
		matchesResult = db.query(matchesQuery);
		boolean hasTeamB = false;
		int goalsBA =0;
		int goalsBB =0;
		int matchIdB = 0;
		if(matchesResult.next()){
			goalsBA = matchesResult.getInt("goalsa");
			goalsBB = matchesResult.getInt("goalsb");
			matchIdB = matchesResult.getInt("id");
		}
		
		if(((goalsAA-goalsAB)>(goalsBB-goalsBA))){
			biggestWin1.setHome(true);
			biggestWin1.setID(matchIdA);
		}else{
			biggestWin1.setHome(false);
			biggestWin1.setID(matchIdB);
		}
		
		stats.setBiggestWin(biggestWin1);
	}
	
	private static void grabBiggestDefeat(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		ObjectFactory factory = new ObjectFactory();
		com.bafa.objects.schema.Score biggestWin1 = factory.createScore();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from matches where ");
		sql.append("teama ="+teamId);
		sql.append(" order by goalsb-goalsa desc");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		
		int goalsAA =0;
		int goalsAB =0;
		int matchIdA = 0;
		if(matchesResult.next()){
			goalsAA = matchesResult.getInt("goalsa");
			goalsAB = matchesResult.getInt("goalsb");
			matchIdA = matchesResult.getInt("id");
		}
		
		
		sql = new StringBuffer();
		sql.append("select * from matches where ");
		sql.append("teamb ="+teamId);
		sql.append(" order by goalsa-goalsb desc");
		matchesQuery.setQueryString(sql.toString());
		matchesResult = db.query(matchesQuery);
		int goalsBA =0;
		int goalsBB =0;
		int matchIdB = 0;
		if(matchesResult.next()){
			goalsBA = matchesResult.getInt("goalsa");
			goalsBB = matchesResult.getInt("goalsb");
			matchIdB = matchesResult.getInt("id");
		}
		
		
		if(((goalsAB-goalsAA)>(goalsBA-goalsBB))){
			biggestWin1.setHome(true);
			biggestWin1.setID(matchIdA);
		}else{
			biggestWin1.setHome(false);
			biggestWin1.setID(matchIdB);
		}
		
		stats.setBiggestDefeat(biggestWin1);
	}
	
	private static void grabMostScored(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		ObjectFactory factory = new ObjectFactory();
		com.bafa.objects.schema.Score biggestWin1 = factory.createScore();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from matches where ");
		sql.append("teama ="+teamId);
		sql.append(" order by goalsa desc");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		int goalsAA =0;
		int matchIdA = 0;
		if(matchesResult.next()){
			goalsAA = matchesResult.getInt("goalsa");
			matchIdA = matchesResult.getInt("id");
		}
		
		
		sql = new StringBuffer();
		sql.append("select * from matches where ");
		sql.append("teamb ="+teamId);
		sql.append(" order by goalsb desc");
		matchesQuery.setQueryString(sql.toString());
		matchesResult = db.query(matchesQuery);
		int goalsBB =0;
		int matchIdB = 0;
		if(matchesResult.next()){
			goalsBB = matchesResult.getInt("goalsb");
			matchIdB = matchesResult.getInt("id");
		}
		
		if((goalsAA>goalsBB)){
			biggestWin1.setHome(true);
			biggestWin1.setID(matchIdA);
		}else{
			biggestWin1.setHome(false);
			biggestWin1.setID(matchIdB);
		}
		
		stats.setMostScored(biggestWin1);
	}
	
	
	private static void grabTotalGoalsScored(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(goalsb) from matches where ");
		sql.append("teamb ="+teamId);
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		int scored = 0;
		scored = matchesResult.getInt(1);
		
		sql = new StringBuffer();
		sql.append("select sum(goalsa) from matches where ");
		sql.append("teama ="+teamId);
		matchesQuery.setQueryString(sql.toString());
		matchesResult = db.query(matchesQuery);
		matchesResult.next();
		scored = scored + matchesResult.getInt(1);
		//set all time total clean sheets
		stats.setGoalsScored(scored);
	}
	
	private static void grabTotalGoalsConceded(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select sum(goalsb) from matches where ");
		sql.append("teama ="+teamId);
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		int conceded = 0;
		conceded = matchesResult.getInt(1);
		
		sql = new StringBuffer();
		sql.append("select sum(goalsa) from matches where ");
		sql.append("teamb ="+teamId);
		matchesQuery.setQueryString(sql.toString());
		matchesResult = db.query(matchesQuery);
		matchesResult.next();
		conceded = conceded + matchesResult.getInt(1);
		//set all time total clean sheets
		stats.setGoalsConceded(conceded);
	}
	
	private static void grabTotalGamesWon(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from matches where ");
		sql.append("(teamb ="+teamId);
		sql.append(" and played=1 and goalsb>goalsa) or ");
		sql.append("(teama ="+teamId);
		sql.append(" and played=1 and goalsa>goalsb)");
		
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		stats.setGamesWon(matchesResult.getInt(1));	
	}
	
	private static void grabTotalGamesDrawn(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		
		StringBuffer sql = new StringBuffer();
		
		sql = new StringBuffer();
		sql.append("select count(*) from matches where ");
		sql.append("(teamb ="+teamId);
		sql.append(" and played=1 and goalsb=goalsa) or ");
		sql.append("(teama ="+teamId);
		sql.append(" and played=1 and goalsa=goalsb)");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		stats.setGamesDrawn(matchesResult.getInt(1));
	}
	private static void grabTotalGamesLost(DBAccess db, TeamStats stats, int teamId)throws Exception{
		QueryObject matchesQuery= new QueryObject("matches");
		StringBuffer sql = new StringBuffer();
		
		sql = new StringBuffer();
		sql.append("select count(*) from matches where ");
		sql.append("(teamb ="+teamId);
		sql.append(" and played=1 and goalsb<goalsa) or ");
		sql.append("(teama ="+teamId);
		sql.append(" and played=1 and goalsa<goalsb)");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		stats.setGamesLost(matchesResult.getInt(1));
	}
	
	private static void grabTotalGamesPlayed(DBAccess db, TeamStats stats, int teamId)throws Exception{
		
		QueryObject matchesQuery= new QueryObject("matches");
		StringBuffer sql = new StringBuffer();
		sql = new StringBuffer();
		sql.append("select count(*) from matches where ");
		sql.append("(teamb ="+teamId);
		sql.append(" and played=1 ) or ");
		sql.append("(teama ="+teamId);
		sql.append(" and played=1 )");
		matchesQuery.setQueryString(sql.toString());
		ResultSet matchesResult = db.query(matchesQuery);
		matchesResult.next();
		stats.setGamesPlayed(matchesResult.getInt(1));
	}
	

	
	
	private static void grabAllTimeGoalScorers(DBAccess db,int teamId,TeamStats stats)throws Exception{
		ObjectFactory factory = new ObjectFactory();
		
		QueryObject scorersQuery = new QueryObject("scorers");
		StringBuffer buffer = new StringBuffer();
		buffer.append("select distinct scorername, sum(numberofgoals) totalgoals from scorers where teamid=");
		buffer.append(teamId);
		buffer.append(" group by scorername order by totalgoals desc");
		scorersQuery.setQueryString(buffer.toString());
		ResultSet scorersResult = db.query(scorersQuery);
		while(scorersResult.next()){
			Scorer scorer = factory.createScorer();
			String name = scorersResult.getString(1);
			int goals = scorersResult.getInt("totalgoals");
			scorer.setGoals(goals);
			scorer.setName(name);
			scorer.setTeamId(teamId);
			scorer.setSeasonId(0);
			stats.getScorer().add(scorer);
		}
	}
	
	
	

	
	private static void grabAllTimeMatchRecords(DBAccess db, int seasonId, TeamSeason teamSeason,TeamStats stats)throws Exception{
		//select * from matches where teama=12 order by goalsa desc,goalsb asc
		//select * from matches where teamb=12 order by goalsb desc,goalsa asc
		
		
	}
	public static TreeMap getTeamstats() {
		return teamstats;
	}
	
	public static void main(String[] args) {
		try{
			grabTeamStats(new DBAccess());
		}catch(Exception e){
			
		}
	}
}
